Introduction

In 2021 I carried a case study for a German start-up. The aim was to calculate the Return on Ads Spending (ROAS) for several Facebook campaigns across the globe and delivering with key insights and recommendations for the upper management. There were two main channels for campaigns spending, the Facebook App and the Facebook website.

The ROAS is a KPI that represents the ratio of the total revenue that can be attributed to a specific campaign divided by the total expenditure of the campaign. My client defined 0.9 as the threshold - a ROAS higher than 0.9 was set as an acceptable return on investment.

More concisely:

  ROAS = TOTAL CAMPAIGN REVENUE / CAMPAIGN COST 

Where:

  Revenue = all attributable subscriptions by campaign 
  Cost = All cost of the campaign

To calculate the ROAS, I performed several postgreSQL queries on their servers.

Facebook Web

Query 1: Facebook Web


                   select c.sub_region, 
                   e.campaign_id,
                   sum(a.revenue) as revenue,
                   g.spend as spend 
                   from  
                   fbt_conversions_web as a,
                   fbt_web_user_mapping as b,
                   fbt_web_sessions as c,
                     (select distinct d.campaign_id
                     from fbt_facebook_campaigns d) as e,
                     (select sub_region, campaign_id, sum(spend) as spend
                     from fbt_facebook_campaigns f
                     group by 
                     sub_region, 
                     campaign_id) as g
                   where 
                   a.sk_user = b.sk_user 
                   and 
                   b.sk_web_user = c.sk_web_user 
                   and 
                   c.campaign_id = e.campaign_id 
                   and 
                   c.sub_region = g.sub_region 
                   and       
                   c.campaign_id = g.campaign_id
                    group by
                   e.campaign_id, 
                   c.sub_region,
                   g.spend;  
                


Results


# A tibble: 5 × 6
   ...1 sub_region                campaign_id revenue spend WEB_ROAS
  <dbl> <chr>                           <dbl>   <dbl> <dbl>    <dbl>
1     0 Australasia & Asia             280020   7359.  7213    1.02 
2     1 Northern America               280020  20774. 28836    0.720
3     2 Northern & Western Europe      280020  10220. 16404    0.623
4     3 South & Central America        280020   4219.  4623    0.913
5     4 Southern & Eastern Europe      280020   8425. 11906    0.708


The Facebook web campaign n° 280020 had a ROAS of 0.8, falling below the threshold defined by the client. However, there was substantial variation across regions. Particularly, Australasia & Asia and South & Central America are markets with a ROAS higher than 0.9.

Visualization: Web App Campaign

The overall performance of the web campaign was 0.8. Regionwise, the Northern & Western Europe region had the a weakest performance (0.62). On the other side, Australasia & Asia had the stronges ROAS of 1.02.

Figure 1: Web channel ROAS


Blue line =  mean Facebook Web ROAS
Red line = 0.9 threshold



Facebook App

Query 2: Facebook App


                  c.campaign_id,
                  sum(a.revenue) rev_web,
                  d.revenue_app,
                  e.spend 
                  from 
                  fbt_conversions_web a,
                  fbt_installs b,
                      (select distinct campaign_id
                      from 
                      fbt_facebook_campaigns) c,
                      (select fbt_installs.campaign_id,
                      sum(fbt_conversions_app.revenue) revenue_app
                      from 
                      fbt_conversions_app,
                      fbt_installs
                      where 
                      fbt_conversions_app.sk_user = fbt_installs.sk_user
                      group by 
                      fbt_installs.campaign_id 
                      ) d,
                      (select sum(spend) spend,campaign_id
                      from fbt_facebook_campaigns
                      group by campaign_id) e
                  where
                  a.sk_user =b.sk_user 
                  and
                  b.campaign_id =c.campaign_id
                  and 
                  c.campaign_id = d.campaign_id
                  and 
                  c.campaign_id = e.campaign_id
                  group by
                  e.spend,
                  d.revenue_app,
                  c.campaign_id;


Results: App campaigns ROAS


# A tibble: 3 × 6
   ...1 campaign_id rev_web revenue_app  spend  ROAS
  <dbl>       <dbl>   <dbl>       <dbl>  <dbl> <dbl>
1     0      339703    654.      44215.  46391 0.967
2     1      379581    267.      24028.  63239 0.384
3     2      279755    987.     155108. 155666 1.00 


Each Facebook App campaign had a different performance. Clarly, the campaign 379581 fell drastically below the expected ROAS. But how was the geographic distribution of these performances? Are they all equal? Following the previous results, I expected to get high variation.


Query 3: App campaigns - Regional variation


                 select 
                      f.sub_region, 
                      f.campaign_id,
                      sum(a.revenue) app_revenue, 
                      f.spend spend
                  from 
                      fbt_conversions_app a, 
                      fbt_installs b,
                          (select  
                           distinct c.campaign_id
                           from fbt_facebook_campaigns c) d,
                           (select   
                              sub_region, campaign_id, sum(spend) spend
                           from fbt_facebook_campaigns
                           group by 
                               sub_region, 
                               campaign_id) f
                  where 
                      a.sk_user = b.sk_user 
                  and 
                      b.campaign_id = d.campaign_id
                  and 
                      b.campaign_id =f.campaign_id
                  and 
                      b.sub_region = f.sub_region
                  group by 
                      f.campaign_id, 
                      f.sub_region,
                      f.spend;


Results: App campaigns ROAS by region


# A tibble: 11 × 6
    ...1 sub_region                campaign_id app_revenue spend APP_ROAS
   <dbl> <chr>                           <dbl>       <dbl> <dbl>    <dbl>
 1     0 Australasia & Asia             279755      13213. 12651    1.04 
 2     1 Northern America               279755      40209. 49692    0.809
 3     2 Northern & Western Europe      279755      65595. 52304    1.25 
 4     3 South & Central America        279755      19061. 23106    0.825
 5     4 Southern & Eastern Europe      279755      17030. 17913    0.951
 6     5 Australasia & Asia             339703       2542.  2488    1.02 
 7     6 Northern America               339703      10074.  8644    1.17 
 8     7 Northern & Western Europe      339703      21642. 23544    0.919
 9     8 South & Central America        339703       5626.  6107    0.921
10     9 Southern & Eastern Europe      339703       4331.  5608    0.772
11    10 Northern America               379581      23685. 63239    0.375


The mean ROAS for the Facebook App campaigns was of 0.91, being 0.11 points higher than of the Facebook Page. However, the table shows great variation across regions. Australasia & Asia have a ROAS higher than 0.9, which is consistent with the Facebook website campaign results. The Northern & Western Europe region had a healthy performance on the two app campaigns. Southern & Eastern Europe, South & Central America and Northern America fell in an intermediate category, with some campaigns underperforming whereas others showed positive results.

Visualizations: Facebook App Campaign


The following plots display the ROAS of the App campaigns by regions. Remember that the overall performance of the Facebook App channel ROAS is 0.91. The plot shows, however, that campaign ID 37981, in North America, underperformed with a ROAS of 0.37.

Figure 2: Facebook App campaigns ROAS


Blue line =  mean Facebook App ROAS
Red line = 0.9 threshold




Recommendations for upper management

Three main patterns were found in the analysis:

  1. That App campaigns perform 13,75% better than web ads.

  2. Web campaigns have an average ROAS of 0.8, with a minimum of 0.62 (Northern & Western Europe) and a maximum ROAS value of 1.02 (Australasia & Asia).

  3. App campaigns had an average of 0.91, with a minimum ROAS of 0.38 (Campaign id 379581 - Northern America ) and a maximum of 1.25 (Campaign id 279755 - Northern & Western Europe).

Recommendations:

  1. Increase spending in App campaigns. Focus spending on regions more responsive to App advertising such as “Northern & Western Europe” and “Australasia & Asia.”

  2. Decrease spending in the lower-performing regions, specifically in the “Northern & Western Europe” and in “Southern & Eastern Europe.”

Contact info



Sebastián Huneeus


shuneeus@gmail.com

Linkedin

Thank you!